package cn.netuo.bumbersoll.db;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.support.annotation.Nullable;
import android.text.TextUtils;
import android.view.View;

import com.rooten.base.DBBase;

import java.util.ArrayList;

import cn.netuo.bumbersoll.entity.Cmd;
import cn.netuo.bumbersoll.entity.VideoAd;
import cn.netuo.bumbersoll.util.BsUtil;
import lib.grasp.util.L;

/**
 * 广告视频表
 */
public class TableVideoAd extends DBBase {

    private final String    TB_NAME   	            = "tb_video_ad";

    private final String    COL_URL                 = "url";            // url
    private final String    COL_NAME                = "name";           // 文件名称
    private final String    COL_MD5                 = "md5";            // md5
    private final String    COL_INDBTIME            = "indbtime";       // 入库时间
    private final String    COL_STATE               = "state";          // 下载状态

    public TableVideoAd() {
        createTable();
    }

    // 建表
    private boolean createTable() {
        if (tableExists(TB_NAME) && !hasColumn(TB_NAME, COL_URL)) {    // 存在表结构不对的同名表
            dropTable(TB_NAME);
            return createTable();
        }
        else if (tableExists(TB_NAME)) {    // 存在表
            return true;
        }
        try {
            String strFmt = "CREATE TABLE %s ("
                    + "%s TEXT PRIMARY KEY,"        //主键
                    + "%s TEXT ,"
                    + "%s TEXT ,"
                    + "%s BIGINT ,"
                    + "%s INT "
                    + ");";

            String strSql = String.format(strFmt, 	TB_NAME,
                    COL_URL             ,
                    COL_NAME            ,
                    COL_MD5             ,
                    COL_INDBTIME        ,
                    COL_STATE
            );
            L.logOnly("建表--SQL:" + strSql);
            execSQL(strSql);
            return true;
        }
        catch (SQLException e) {
            L.logOnly("SQLException" + e.getMessage());
        }
        return false;
    }

    //删表
    public void dropTable() {
        if (tableExists(TB_NAME)) dropTable(TB_NAME);
    }

    //增/改
    public void importArg(VideoAd videoAd) {
        if(videoAd == null) return;
        ContentValues cv = new ContentValues();
        cv.put(COL_URL      		, videoAd.url       );
        cv.put(COL_NAME     		, videoAd.name      );
        cv.put(COL_MD5      		, videoAd.md5       );
        cv.put(COL_INDBTIME 		, videoAd.indbtime  );
        cv.put(COL_STATE      	    , videoAd.state     );

//        insert(TB_NAME, cv);
        replace(TB_NAME, cv);
    }

    /** 查询最晚批次的视频任务(是否只查没下好的), 返回的是个实体(-1则表示全查) */
    @Nullable
    public ArrayList<VideoAd> queryLastVideoAdByLoadState(int targetState) {
        ArrayList<VideoAd> list = new ArrayList<>();
        String sql = "select %s from %s WHERE %s = (SELECT MAX(%s) FROM %s)" + ((targetState > -1) ? "AND %s = %s" : "") + ";";
        sql = String.format(sql, "*", TB_NAME, COL_INDBTIME, COL_INDBTIME, TB_NAME, COL_STATE, targetState);
        L.logOnly("查--SQL:"+sql);

        Cursor result = rawQuery(sql);
        try {
            for (result.moveToFirst(); !result.isAfterLast(); result.moveToNext()){  // 使用游标获取结果{
                String  url             = result.getString(result.getColumnIndex(COL_URL      ));
                String  name            = result.getString(result.getColumnIndex(COL_NAME     ));
                String  md5             = result.getString(result.getColumnIndex(COL_MD5      ));
                long    indbTime        = result.getLong(result.getColumnIndex(COL_INDBTIME ));
                int     state           = result.getInt(result.getColumnIndex(COL_STATE     ));

                VideoAd videoAd = new VideoAd();
                videoAd.url     = url       ;
                videoAd.name    = name      ;
                videoAd.md5     = md5       ;
                videoAd.indbtime= indbTime  ;
                videoAd.state   = state     ;
                list.add(videoAd);
            }
        }
        catch (Exception e){
            L.logOnly("Exception--" + e);
        }
        finally {
            closeAll(result);
        }
        return list;
    }

    /** 只是查state */
    @Nullable
    public int queryState(String url) {
        if(TextUtils.isEmpty(url)) return 0;

        String sql = "select %s from %s WHERE %s = '%s'";
        sql = String.format(sql, COL_STATE, TB_NAME, COL_URL, url);
        L.logOnly("查--SQL:"+sql);

        Cursor result = rawQuery(sql);
        try {
            for (result.moveToFirst(); !result.isAfterLast(); result.moveToNext()){  // 使用游标获取结果{
                return result.getInt(result.getColumnIndex(COL_STATE     ));
            }
        }
        catch (Exception e){
            L.logOnly("Exception--" + e);
        }
        finally {
            closeAll(result);
        }
        return 0;
    }


    /** 改(将指定的视频任务设置他的入库时间), 有则该, 无则增 */
    public boolean updateOrInsert(VideoAd videoAd) {
        if(videoAd == null) return false;
        try{
            String sql = "replace into %s values ('%s', '%s', '%s', %s, %s);";
            sql = String.format(sql, TB_NAME, videoAd.url, videoAd.name, videoAd.md5, videoAd.indbtime, videoAd.state);
            L.logOnly("改--SQL:" + sql);
            execSQL(sql);
            return true;
        }
        catch (Exception e){
            L.logOnly("Exception" + e.getMessage());
            return false;
        }
    }

    /** 改(将指定的视频任务设置他的下载状态) */
    public boolean updateArgValue(String url, int targetState) {
        if(TextUtils.isEmpty(url)) return false;
        try{
            String sql = "update %s set %s = %s where %s = '%s';";
            sql = String.format(sql, TB_NAME, COL_STATE, targetState, COL_URL, url);
            L.logOnly("改--SQL:" + sql);
            execSQL(sql);
            return true;
        }
        catch (Exception e){
            L.logOnly("Exception" + e.getMessage());
            return false;
        }
    }

    /** 删(删除最晚之前的所有) */
    public void deleteEarly() {
        String sql = "delete from %s WHERE %s < (SELECT MAX(%s) FROM %s);";
        sql = String.format(sql, TB_NAME, COL_INDBTIME, COL_INDBTIME, TB_NAME);
        L.logOnly("删--SQL:" + sql);
        execSQL(sql);
    }


    /** 删(删除最晚之前的所有) */
    public void deleteAll() {
        String sql = "delete from %s;";
        sql = String.format(sql, TB_NAME);
        L.logOnly("删--SQL:" + sql);
        execSQL(sql);
    }
}
